import os
import sys
import xlrd
import xlsxwriter
# 表头元素规范
listhead=[]
listlen=0
# 在下方输入需要合并的文件所在文件夹位置
inputpath='input'
templatepath='template'
outputpath='output.xlsx'
w1=os.path.dirname(os.path.realpath(sys.executable))
print(w1)
print(os.path.dirname(w1))
if getattr(sys, 'frozen', False):
    application_path = os.path.dirname(os.path.realpath(sys.executable))
    print('aaa'+application_path)
elif __file__:
    application_path = os.path.dirname(__file__)
    print('ppp'+application_path)
# input文件夹路径
path_parent=os.path.dirname(application_path)
path=os.path.join(path_parent,inputpath)
# tmplate文件夹路径
pathtemplate=os.path.join(path_parent,templatepath)
# 在下方输入合并后Excel的路径和文件名
outpath=os.path.join(path_parent,outputpath)
work=xlsxwriter.Workbook(outpath)
# 新建sheet
sheet=work.add_worksheet('combine')
elsesheet=work.add_worksheet('elsesource')
# 读取模板确定表头
file_list_template=os.listdir(pathtemplate)
for file in file_list_template:
    file_name = os.path.join(pathtemplate,file)
    workbook=xlrd.open_workbook(file_name)
    sheet_name=workbook.sheet_names()
    table=workbook.sheet_by_index(0)
# rows=table.nrows
    clos=table.ncols
    listtmp=table.row_values(0)
    listhead+=listtmp
    listlen=clos
# 合并表表头
for i in range(listlen):
    sheet.write_string(0, i, listhead[i])
# 添加一列表示来源
sheet.write_string(0, listlen, '文件来源')
# 设置列宽
sheet.set_column(0,listlen,18)
elsesheet.set_column(0,listlen,18)
# input文件读取
file_list=os.listdir(path)
file_list.sort()
# Main
file_name='';
x1=2; x2=1;elsex1=1;
fileNum = len(file_list)
print("在该目录下有%d个xlsx文件"%fileNum)
for file in file_list:
    file_name = os.path.join(path,file)
    workbook=xlrd.open_workbook(file_name)
    sheet_name=workbook.sheet_names()
    for sheet_1 in sheet_name:
        table=workbook.sheet_by_name(sheet_1)
        rows=table.nrows
        clos=table.ncols      
# 非空表才合并        
        if rows!=0:
# 提取出表头，判断是否规范
            listtmp=table.row_values(0)
            if listhead!=listtmp:
                for i in range(rows):
                    elsesheet.write_row('A'+str(elsex1), table.row_values(i))
                    elsesheet.write_string(elsex1-1, clos, file)
                    elsex1+=1
            else:
                for i in range(1,rows): 
                    sheet.write_row('A'+str(x1),table.row_values(i))
                    sheet.write_string(x1-1, listlen, file)
                    x1+=1
            print(listtmp)

    print('正在合并第%d个文件 '%x2)
    print('已完成 ' + file)
    x2 += 1;
    
print("已将%d个文件合并完成"%fileNum)
work.close()